﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using FX.CC.Andon.Entity.BasicDataManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.BasicDataManage
{
    public class PrdTaskService : DbHelperBase
    {
        public List<PrdTaskEntity> GetAll()
        {
            var list = new List<PrdTaskEntity>();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT FlowID,BizDate,ShiftID,EqID FROM UT_PrdTask AS A  ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new PrdTaskEntity();
                            if (reader["FlowID"] != DBNull.Value)
                                model.FlowID = new Guid(reader["FlowID"].ToString());
                            if (reader["BizDate"] != DBNull.Value)
                                model.BizDate = Convert.ToDateTime(reader["BizDate"].ToString());
                            if (reader["ShiftID"] != DBNull.Value)
                                model.ShiftID = new Guid(reader["ShiftID"].ToString());
                            if (reader["EqID"] != DBNull.Value)
                                model.EqID = new Guid(reader["EqID"].ToString());
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public void BatchAdd(List<PrdTaskEntity> list)
        {
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapTaskId =
                    new SqlBulkCopyColumnMapping("TaskID", "TaskID");
                bcp.ColumnMappings.Add(mapTaskId);
                var mapFlowID =
                    new SqlBulkCopyColumnMapping("FlowID", "FlowID");
                bcp.ColumnMappings.Add(mapFlowID);
                var mapBizDate =
                    new SqlBulkCopyColumnMapping("BizDate", "BizDate");
                bcp.ColumnMappings.Add(mapBizDate);
                var mapShiftId =
                   new SqlBulkCopyColumnMapping("ShiftID", "ShiftID");
                bcp.ColumnMappings.Add(mapShiftId);

                var mapEqId =
                   new SqlBulkCopyColumnMapping("EqID", "EqID");
                bcp.ColumnMappings.Add(mapEqId);

                var mapPlanQty =
                   new SqlBulkCopyColumnMapping("PlanQty", "PlanQty");
                bcp.ColumnMappings.Add(mapPlanQty);

                var mapPlanStartTime =
                   new SqlBulkCopyColumnMapping("PlanStartTime", "PlanStartTime");
                bcp.ColumnMappings.Add(mapPlanStartTime);

                var mapPlanEndTime =
                   new SqlBulkCopyColumnMapping("PlanEndTime", "PlanEndTime");
                bcp.ColumnMappings.Add(mapPlanEndTime);

                var mapRecStatus =
                   new SqlBulkCopyColumnMapping("RecStatus", "RecStatus");
                bcp.ColumnMappings.Add(mapRecStatus);

                bcp.DestinationTableName = "UT_PrdTask";
                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(list));
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }

        public void BatchModify(List<PrdTaskEntity> list)
        {
            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT FlowID,BizDate,ShiftID,EqID,PlanQty,PlanStartTime,PlanEndTime INTO [#Tmp] FROM UT_PrdTask Where 1=2";
                    cmd.ExecuteNonQuery();
                }

                using (var bcp = new SqlBulkCopy(conn))
                {
                    var mapFlowID =
                   new SqlBulkCopyColumnMapping("FlowID", "FlowID");
                    bcp.ColumnMappings.Add(mapFlowID);

                    var mapBizDate =
                        new SqlBulkCopyColumnMapping("BizDate", "BizDate");
                    bcp.ColumnMappings.Add(mapBizDate);

                    var mapShiftId =
                        new SqlBulkCopyColumnMapping("ShiftID", "ShiftID");
                    bcp.ColumnMappings.Add(mapShiftId);

                    var mapEqId =
                       new SqlBulkCopyColumnMapping("EqID", "EqID");
                    bcp.ColumnMappings.Add(mapEqId);

                    var mapPlanQty =
                      new SqlBulkCopyColumnMapping("PlanQty", "PlanQty");
                    bcp.ColumnMappings.Add(mapPlanQty);

                    var mapPlanStartTime =
                      new SqlBulkCopyColumnMapping("PlanStartTime", "PlanStartTime");
                    bcp.ColumnMappings.Add(mapPlanStartTime);

                    var mapPlanEndTime =
                      new SqlBulkCopyColumnMapping("PlanEndTime", "PlanEndTime");
                    bcp.ColumnMappings.Add(mapPlanEndTime);

                    bcp.DestinationTableName = "#Tmp";

                    try
                    {
                        bcp.WriteToServer(Collection.ToDataTable(list));
                    }
                    catch (Exception ex)
                    {
                        EventsLog.WriteLog("ErrorInfo" + ex.Message);
                    }
                }

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"Update A Set A.PlanQty=B.PlanQty,A.PlanStartTime=B.PlanStartTime,A.PlanEndTime=B.PlanEndTime From #Tmp B Inner Join UT_PrdTask A ON A.FlowID=B.FlowID AND A.BizDate=B.BizDate AND A.ShiftID=B.ShiftID AND A.EqID=B.EqID ;Drop Table #Tmp;";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public int GetPrdTaskCount(PrdTaskQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT count(*) AS [count] FROM UT_PrdTask AS A   ");
            strSql.Append(" LEFT JOIN UT_Flow AS B ON A.FlowID=B.FlowID  ");
            strSql.Append(" LEFT JOIN UT_Shift AS C ON A.ShiftID=C.ShiftID ");
            strSql.Append(" LEFT JOIN UT_Equipment AS D ON A.EqID=D.EqID ");
            strSql.Append(" LEFT JOIN UT_Product AS F ON B.PrdID=F.PrdID ");
            strSql.Append("  LEFT JOIN UT_Line AS G ON D.LineID=G.LineID ");
            strSql.Append(" where 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" and A.BizDate>=@BizDateFrom");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" and A.BizDate<=@BizDateEnd");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqID))
            {
                strSql.Append(" and A.EqID=@EqID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.ShiftID))
            {
                strSql.Append(" and A.ShiftID=@ShiftID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdCode))
            {
                strSql.Append(" and F.PrdCode LIKE '%" + qcmodel.PrdCode + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" and G.LineID=@LineID ");
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        var date = Convert.ToDateTime(qcmodel.BizDateFrom);
                        Db.AddInParameter(cmd, "@BizDateFrom", date);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        var date = Convert.ToDateTime(qcmodel.BizDateEnd);
                        Db.AddInParameter(cmd, "@BizDateEnd", date);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.EqID))
                    {
                        Db.AddInParameter(cmd, "@EqID", qcmodel.EqID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.ShiftID))
                    {
                        Db.AddInParameter(cmd, "@ShiftID", qcmodel.ShiftID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.LineID))
                    {
                        Db.AddInParameter(cmd, "@LineID", qcmodel.LineID);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<PrdTaskEntity> GetPrdTaskList(PrdTaskQcEntity qcmodel)
        {
            var list = new List<PrdTaskEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.PlanStartTime desc) AS idx,A.TaskID,A.FlowID,B.ProcessIdx,F.PrdID,F.PrdCode,F.PrdName,A.BizDate,A.ShiftID,C.ShiftName,A.EqID,D.EqCode,D.EqName,A.PlanQty,A.PlanStartTime,A.PlanEndTime,A.RecStatus,G.LineCode,G.LineName FROM UT_PrdTask AS A    ");
            strSql.Append(" LEFT JOIN UT_Flow AS B ON A.FlowID=B.FlowID  ");
            strSql.Append(" LEFT JOIN UT_Shift AS C ON A.ShiftID=C.ShiftID ");
            strSql.Append(" LEFT JOIN UT_Equipment AS D ON A.EqID=D.EqID ");
            strSql.Append(" LEFT JOIN UT_Product AS F ON B.PrdID=F.PrdID ");
            strSql.Append("  LEFT JOIN UT_Line AS G ON D.LineID=G.LineID ");
            strSql.Append(" where 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" and A.BizDate>=@BizDateFrom");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" and A.BizDate<=@BizDateEnd");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqID))
            {
                strSql.Append(" and A.EqID=@EqID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.ShiftID))
            {
                strSql.Append(" and A.ShiftID=@ShiftID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdCode))
            {
                strSql.Append(" and F.PrdCode LIKE '%" + qcmodel.PrdCode + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" and G.LineID=@LineID ");
            }
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {

                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        var date = Convert.ToDateTime(qcmodel.BizDateFrom);
                        Db.AddInParameter(cmd, "@BizDateFrom", date);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        var date = Convert.ToDateTime(qcmodel.BizDateEnd);
                        Db.AddInParameter(cmd, "@BizDateEnd", date);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.EqID))
                    {
                        Db.AddInParameter(cmd, "@EqID", qcmodel.EqID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.ShiftID))
                    {
                        Db.AddInParameter(cmd, "@ShiftID", qcmodel.ShiftID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.LineID))
                    {
                        Db.AddInParameter(cmd, "@LineID", qcmodel.LineID);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new PrdTaskEntity();
                            if (reader["TaskID"] != DBNull.Value)
                                model.TaskID = new Guid(reader["TaskID"].ToString());
                            if (reader["FlowID"] != DBNull.Value)
                                model.FlowID = new Guid(reader["FlowID"].ToString());
                            if (reader["PrdID"] != DBNull.Value)
                                model.PrdID = new Guid(reader["PrdID"].ToString());
                            model.PrdCode = reader["PrdCode"] == obj ? null : Convert.ToString(reader["PrdCode"]);
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            if (reader["BizDate"] != DBNull.Value)
                                model.BizDate = Convert.ToDateTime(reader["BizDate"].ToString());
                            if (reader["ShiftID"] != DBNull.Value)
                                model.ShiftID = new Guid(reader["ShiftID"].ToString());
                            model.ShiftName = reader["ShiftName"] == obj ? null : Convert.ToString(reader["ShiftName"]);
                            if (reader["EqID"] != DBNull.Value)
                                model.EqID = new Guid(reader["EqID"].ToString());
                            model.EqCode = reader["EqCode"] == obj ? null : Convert.ToString(reader["EqCode"]);
                            model.EqName = reader["EqName"] == obj ? null : Convert.ToString(reader["EqName"]);
                            if (reader["PlanQty"] != DBNull.Value)
                                model.PlanQty = Convert.ToInt32(reader["PlanQty"].ToString());
                            if (reader["PlanStartTime"] != DBNull.Value)
                                model.PlanStartTime = Convert.ToDateTime(reader["PlanStartTime"].ToString());
                            if (reader["PlanEndTime"] != DBNull.Value)
                                model.PlanEndTime = Convert.ToDateTime(reader["PlanEndTime"].ToString());
                            if (reader["RecStatus"] != DBNull.Value)
                                model.RecStatus = Convert.ToBoolean(reader["RecStatus"]);
                            model.LineCode = reader["LineCode"] == obj ? null : Convert.ToString(reader["LineCode"]);
                            model.LineName = reader["LineName"] == obj ? null : Convert.ToString(reader["LineName"]);
                            if (reader["ProcessIdx"] != DBNull.Value)
                                model.ProcessIdx = Convert.ToInt32(reader["ProcessIdx"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public PrdTaskEntity GetPrdTaskInfo(string id)
        {
            var model = new PrdTaskEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append("  SELECT A.[TaskID],C.LineID,C.LineName,F.[PrdID],F.PrdName,A.[BizDate],A.[ShiftID],D.ShiftName,A.[EqID],B.EqName,A.[PlanQty],A.[PlanStartTime],A.[PlanEndTime],E.FlowID,E.ProcessIdx FROM [UT_PrdTask] AS A  ");
            strSql.Append("  LEFT JOIN UT_Equipment AS B ON A.EqID=B.EqID  ");
            strSql.Append("  LEFT JOIN UT_Line AS C ON B.LineID=C.LineID  ");
            strSql.Append("  LEFT JOIN UT_Shift AS D ON A.ShiftID=D.ShiftID  ");
            strSql.Append("  LEFT JOIN UT_Flow AS E ON A.FlowID=E.FlowID  ");
            strSql.Append("   LEFT JOIN UT_Product AS F ON E.PrdID=F.PrdID  ");
            strSql.Append(" WHERE A.TaskID=@TaskID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@TaskID", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["TaskID"] != DBNull.Value)
                                model.TaskID = new Guid(reader["TaskID"].ToString());
                            if (reader["LineID"] != DBNull.Value)
                                model.LineID = new Guid(reader["LineID"].ToString());
                            model.LineName = reader["LineName"] == obj ? null : Convert.ToString(reader["LineName"]);
                            if (reader["PrdID"] != DBNull.Value)
                                model.PrdID = new Guid(reader["PrdID"].ToString());
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            if (reader["BizDate"] != DBNull.Value)
                                model.BizDate = Convert.ToDateTime(reader["BizDate"].ToString());
                            if (reader["ShiftID"] != DBNull.Value)
                                model.ShiftID = new Guid(reader["ShiftID"].ToString());
                            model.ShiftName = reader["ShiftName"] == obj ? null : Convert.ToString(reader["ShiftName"]);
                            if (reader["EqID"] != DBNull.Value)
                                model.EqID = new Guid(reader["EqID"].ToString());
                            model.EqName = reader["EqName"] == obj ? null : Convert.ToString(reader["EqName"]);
                            if (reader["PlanQty"] != DBNull.Value)
                                model.PlanQty = Convert.ToInt32(reader["PlanQty"].ToString());
                            if (reader["PlanStartTime"] != DBNull.Value)
                                model.PlanStartTime = Convert.ToDateTime(reader["PlanStartTime"].ToString());
                            if (reader["PlanEndTime"] != DBNull.Value)
                                model.PlanEndTime = Convert.ToDateTime(reader["PlanEndTime"].ToString());
                            if (reader["FlowID"] != DBNull.Value)
                                model.FlowID = new Guid(reader["FlowID"].ToString());
                            if (reader["ProcessIdx"] != DBNull.Value)
                                model.ProcessIdx = Convert.ToInt32(reader["ProcessIdx"].ToString());
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool ModifyPrdTask(PrdTaskEntity model)
        {
            bool result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [UT_PrdTask] SET [FlowID] = @FlowID,[BizDate] = @BizDate,[ShiftID] = @ShiftID,[EqID] = @EqID,[PlanQty] = @PlanQty,[PlanStartTime] = @PlanStartTime,[PlanEndTime] = @PlanEndTime WHERE [TaskID] = @TaskID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@FlowID", model.FlowID);
                    Db.AddInParameter(cmd, "@BizDate", model.BizDate);
                    Db.AddInParameter(cmd, "@ShiftID", model.ShiftID);
                    Db.AddInParameter(cmd, "@EqID", model.EqID);
                    Db.AddInParameter(cmd, "@PlanQty", model.PlanQty);
                    Db.AddInParameter(cmd, "@PlanStartTime", model.PlanStartTime);
                    Db.AddInParameter(cmd, "@PlanEndTime", model.PlanEndTime);
                    Db.AddInParameter(cmd, "@TaskID", model.TaskID);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
    }
}
